A SQL statement can perform poorly because the query optimizer selects a sub-optimal execution plan for the statement. Executing the sub-optimal plan can have a catastrophic impact on the performance of an application program running on the computer system. For example, poor execution plans often consume too much system resources like CPU, I/O, temporary disk space and memory. As a result, the entire application throughput can be impacted up to the point where the application is no longer functioning to an acceptable level. In fact, a single poorly performing SQL statement has the potential to choke a database system and bring it down to its knees.
Usually, an optimizer selects a poor SQL execution plan because it lacks specific knowledge about the SQL statement to be optimized. For example, information about when the statement is executed is not available to the optimizer. As a result, the optimizer fails to select a plan to optimize a statement executed during peak hours using a goal of limiting resource consumption, and fails to optimize a plan for a statement that is executed during batch time using a goal of improving its response time.
Information about how the statement is executed is also typically missing. For example, if users are fetching all the rows from that statement, then the execution plan for the statement should return all rows of results. If only the first few rows of that query are fetched, then the execution plan for the statement can be optimized to return a few rows of the result. However, if this information is missing, or is supplied by a global parameter that is used by the optimizer for all statements, then this optimization decision is not made for that individual query.
Other information, such as if some objects accessed by that SQL statement are volatile, and whether default assumptions and estimates are accurate, is also typically missing. For example, a plan for executing a volatile object that uses dynamic sampling techniques, instead of relying on stored statistics, is not selected, because the optimizer is unaware of the volatility. Furthermore, if default assumptions made by the optimizer to estimate intermediate result cardinalities are inaccurate for that statement, the optimizer produces a sub-optimal plan. For example, the optimizer can assume predicate independence (i.e. no correlation), when correlation actually exists. Default assumptions for estimating access path cost may also be incorrect, causing a sub-optimal plan to be selected for a particular SQL statement.